/***************************************************************
*  Copyright (c) 2007 by GroupMe! Team (www.groupme.net)
*  All rights reserved.
*
*  This file is part of the GroupMe! Project. Source code of 
*  this project is closed and redistribution of this code is
*  prohibited. 
*  
*  Contact: http://www.groupme.net
*
*  This copyright notice MUST APPEAR in all copies of the file!
***************************************************************/
package net.groupme.utils.sql;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;

import javax.sql.DataSource;

import net.groupme.exceptions.InsertFailedException;
import net.groupme.exceptions.search.SearchEngineException;
import net.groupme.model.Group;
import net.groupme.model.ObjectFactory;
import net.groupme.model.Resource;
import net.groupme.model.Tag;
import net.groupme.model.User;
import net.groupme.model.dao.DATABASE_CONSTANTS;
import net.groupme.search.strategy.model.WeightedIdentifiableComparatorByWeight;
import net.groupme.search.strategy.model.WeightedTag;
import net.groupme.utils.StringUtilities;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.object.MappingSqlQuery;

/**
 * created on Jul 6, 2007
 * 
 * This class provides some helper functions to query special stuff
 * from the tagging context table (e.g. querying for tag clouds).
 * 
 * @author Fabian Abel, <a href="mailto:abel@l3s.de">abel@l3s.de</a>
 * @author last edited by: $Author: mischa $
 * 
 * @version $Revision: 1.10 $ $Date: 2008-05-15 11:57:31 $
 */
public class TagQueryingUtility {

	/** Logger for this class and subclasses */
	protected final Log logger = LogFactory.getLog(getClass());
	
	/**
	 * Small helper function to extract {@link Tag} instances from a given keyword query string.
	 * @param keywords the keywords as a string which may look like this: <i>"Semantic Web" OWL RDF</i>
	 * @return a collection of separated tags which may look like this: <i>[Semantic Web, OWL, RDF]</i>
	 * @throws SearchEngineException thrown if creation/receiving of {@link Tag} instance fails
	 * @throws InsertFailedException 
	 */
	public static Collection<Tag> getTags(String keywords) throws SearchEngineException{
		Collection<String> singleKeywords = StringUtilities.extractTags(keywords, false);
		Collection<Tag> tags = new HashSet<Tag>();
		for(String keyword: singleKeywords){
			Tag actualTag;
			try {
				actualTag = ObjectFactory.getTagForKeyword(keyword);
			} catch (InsertFailedException e) {
				e.printStackTrace();
				throw new SearchEngineException("Unable to extract tags...", e);
			}
			if(actualTag != null){
				tags.add(actualTag);
			}
		}
		return tags;
	}
	
	/**
	 * Returns a tag cloud which is an ordered list of weighted tags (all tags are considered) 
	 * 
	 * @param startOfPeriod the start of the period from which tags should be considered
	 * @param endOfPeriod the end of the period until which tags should be considered
	 * @param topK to specify the maximum number of tags that are contained in the tag cloud. If <code>topK</code> = 50 then the 50 most
	 * important tags are returned (50 most popular tags). If <code>topK == null</code> then all tags will be returned (this may take very long).
	 * @return tag cloud which is an ordered list of weighted tags (all tags are considered)
	 */
	@SuppressWarnings("unchecked")
	public List<WeightedTag> queryForTagCloud(Date startOfPeriod, Date endOfPeriod, Integer topK){
		logger.info("Querying for Tags from " + startOfPeriod.toLocaleString() + " till " + endOfPeriod.toLocaleString() + ".");
		List<WeightedTag> cloud = new ArrayList<WeightedTag>();
		String query= "SELECT " + DATABASE_CONSTANTS.TAG_ID + " FROM " + DATABASE_CONSTANTS.TABLE_TAG; //$NON-NLS-1$ //$NON-NLS-2$ 
		
		GetTagsQuery tagQuery = new GetTagsQuery(ObjectFactory.ds, query, DATABASE_CONSTANTS.TAG_ID);
		Iterator iter = tagQuery.execute().iterator();
		if (iter.hasNext()) {
			Collection<Tag> tags = (Collection<Tag>)iter.next();
			for(Tag tag: tags){
				query= "SELECT count(" + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + ") FROM  " //$NON-NLS-1$ //$NON-NLS-2$
					+ DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + " WHERE " //$NON-NLS-1$ 
					+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = '" + tag.getId() + "'"; //$NON-NLS-1$ //$NON-NLS-2$
				if(startOfPeriod != null){
					query += " AND '" + startOfPeriod + "' <= " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_DATE;
				}if(endOfPeriod != null){
					query += " AND " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_DATE + " <= '" + endOfPeriod + "'";
				}
				
				
				GetNumberOfTags countQuery = new GetNumberOfTags(ObjectFactory.ds, query);
				Iterator counts = countQuery.execute().iterator();
				if (counts.hasNext()) {
					cloud.add(new WeightedTag(tag, ((Integer)counts.next()).doubleValue()));
				}
				
			}
		}
		logger.info("Finished querying.");
		Collections.sort(cloud, new WeightedIdentifiableComparatorByWeight());
		if(topK != null && (topK < cloud.size())){
			return cloud.subList(0, topK);
		}
		return cloud;
	}
	
	
	/**
	 * Returns a tag cloud which is an ordered list of weighted tags (all tags are considered)
	 * 
	 * @param startOfPeriod the start of the period from which tags should be considered
	 * @param endOfPeriod the end of the period until which tags should be considered 
	 * @param topK to specify the maximum number of tags that are contained in the tag cloud. If <code>topK</code> = 50 then the 50 most
	 * important tags are returned (50 most popular tags). If <code>topK == null</code> then all tags will be returned (this may take very long).
	 * @return tag cloud which is an ordered list of weighted tags (all tags are considered)
	 */
	@SuppressWarnings("unchecked")
	public List<WeightedTag> queryForUsersTagCloud(User user, Date startOfPeriod, Date endOfPeriod, Integer topK){
		List<WeightedTag> cloud = new ArrayList<WeightedTag>();
		String query= "SELECT distinct " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + //$NON-NLS-1$ //$NON-NLS-2$
						" WHERE " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_USERID + " = '" + user.getId() + "'";

		if(startOfPeriod != null){
			query += " AND '" + startOfPeriod + "' <= " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_DATE;
		}if(endOfPeriod != null){
			query += " AND " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_DATE + " <= '" + endOfPeriod + "'";
		}
		
		GetTagsQuery tagQuery = new GetTagsQuery(ObjectFactory.ds, query, DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID);
		Iterator iter = tagQuery.execute().iterator();
		if (iter.hasNext()) {
			Collection<Tag> tags = (Collection<Tag>)iter.next();
			for(Tag tag: tags){
				query= "SELECT count(" + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + ") FROM  " //$NON-NLS-1$ //$NON-NLS-2$
					+ DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + " WHERE " //$NON-NLS-1$ 
					+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = '" + tag.getId() + "'"; //$NON-NLS-1$ //$NON-NLS-2$
				
				if(startOfPeriod != null){
					query += " AND '" + startOfPeriod + "' <= " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_DATE;
				}if(endOfPeriod != null){
					query += " AND " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_DATE + " <= '" + endOfPeriod + "'";
				}
				
				GetNumberOfTags countQuery = new GetNumberOfTags(ObjectFactory.ds, query);
				Iterator counts = countQuery.execute().iterator();
				if (counts.hasNext()) {
					cloud.add(new WeightedTag(tag, ((Integer)counts.next()).doubleValue()));
				}	
			}
		}
		Collections.sort(cloud, new WeightedIdentifiableComparatorByWeight());
		if(topK != null && (topK < cloud.size())){
			return cloud.subList(0, topK);
		}
		return cloud;
	}

	/**
	 * Returns all direct Tag assignments of a resource (thus there may be duplicates within the 
	 * returned collection).
	 * @param resource the resource whose direct Tag assignments should be returned
	 * @return all direct Tag assignments of a resource (thus there may be duplicates within the 
	 * returned collection)
	 */
	@SuppressWarnings("unchecked")
	public Collection<Tag> getAllTagAssignmentsOfResource(Resource resource){
		String query = "SELECT " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + " WHERE " //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " = " + resource.getId(); //$NON-NLS-1$ 

		GetTagsQuery getTagsQuery = new GetTagsQuery(ObjectFactory.ds, query,
				DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID);

		Iterator iter = getTagsQuery.execute().iterator();
		if (iter.hasNext())
			return (Collection<Tag>) iter.next();
		return new ArrayList<Tag>();
	}
	
	/**
	 * Returns all direct Tag assignments of a resource within a group (thus there may be duplicates within the 
	 * returned collection).
	 * @param resource the resource whose direct Tag assignments should be returned
	 * @param group the group in which the resource was tagged..
	 * @return all direct Tag assignments of a resource within a group (thus there may be duplicates within the 
	 * returned collection)
	 */
	@SuppressWarnings("unchecked")
	public Collection<Tag> getAllTagAssignmentsOfResource(Resource resource, Group group){
		String query = "SELECT " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + " WHERE " //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " = " + resource.getId()+ " AND " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_GROUPID + " = " + group.getId(); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$

		GetTagsQuery getTagsQuery = new GetTagsQuery(ObjectFactory.ds, query,
				DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID);

		Iterator iter = getTagsQuery.execute().iterator();
		if (iter.hasNext())
			return (Collection<Tag>) iter.next();
		return new ArrayList<Tag>();
	}
	
	

	/**
	 * Small helper class to query the amount of tags.
	 */
	public class GetNumberOfTags extends MappingSqlQuery{
		/**
		 * Creates a new GetNumberOfTags instance.
		 * @param ds the data source
		 * @param sql SQL query which has only a count(..) within the select part
		 */
		public GetNumberOfTags(DataSource ds, String sql) {
			super(ds, sql);
		}

		/* (non-Javadoc)
		 * @see org.springframework.jdbc.object.MappingSqlQuery#mapRow(java.sql.ResultSet, int)
		 */
		@Override
		protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			while (!rs.isAfterLast()){
				return rs.getInt(1);
			}
			return null;
		}
		
	}
}
